﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using br.com.SistemaChamados.Model;
using br.com.SistemaChamados;
using br.com.SistemaChamados.Enum;


namespace br.com.SistemaChamados.DAO
{
    public class StateDAO
    {

        private static string SQL_INSERT_STATE = " INSERT INTO TBL_STATE (TBL_STATENAME, TBL_STATEACRONYM) VALUES (@name, @acronym) ";
        private static string SQL_UPDATE_STATE = " UPDATE TBL_STATE SET TBL_STATENAME = @name, TBL_STATEACRONYM = @acronym WHERE TBL_STATEID = @code ";
        private static string SQL_DELETE_STATE = " DELETE FROM TBL_STATE WHERE TBL_STATEID = @code ";
        private static string SQL_GET_STATES = " SELECT * FROM TBL_STATE ";
        private static string SQL_GET_STATE = " SELECT * FROM TBL_STATE WHERE TBL_STATEID = @code";

        public static State getState(State state)
        {
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            State stateReturn = new State();
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_STATE, conn);
                command.Parameters.Add(Mapped.Parameter("@code", state.Code));
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                reader.Read();
                stateReturn.Code = Convert.ToInt32(reader["TBL_STATEID"].ToString());
                stateReturn.Name = Convert.ToString(reader["TBL_STATENAME"].ToString());
                stateReturn.Acronym = Convert.ToString(reader["TBL_STATEACRONYM"].ToString());
            }
            catch (Exception ex)
            {
                stateReturn = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return stateReturn;
        }

        public static List<State> getStates()
        {
            List<State> list = new List<State>();
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            State stateReturn = new State();
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_STATES, conn);
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    stateReturn = new State();
                    stateReturn.Code = Convert.ToInt32(reader["TBL_STATEID"].ToString());
                    stateReturn.Name = Convert.ToString(reader["TBL_STATENAME"].ToString());
                    stateReturn.Acronym = Convert.ToString(reader["TBL_STATEACRONYM"].ToString());
                    list.Add(stateReturn);
                    stateReturn = null;

                    
                }
            }
            catch (Exception ex)
            {
                list = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return list;
        }

        public static EnumGenericState InsertState(State state) {

            EnumGenericState stateTransaction = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;

            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_INSERT_STATE, conn);
                command.Parameters.Add(Mapped.Parameter("@name", state.Name));
                command.Parameters.Add(Mapped.Parameter("@acronym", state.Acronym));
                command.ExecuteNonQuery();
                stateTransaction = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                stateTransaction = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally 
            {
                if (conn != null) conn.Close();
            }

            return stateTransaction;

        }

        public static EnumGenericState UpdateState(State state)
        {

            EnumGenericState stateTransaction = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;

            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_UPDATE_STATE, conn);
                command.Parameters.Add(Mapped.Parameter("@name", state.Name));
                command.Parameters.Add(Mapped.Parameter("@acronym", state.Acronym));
                command.Parameters.Add(Mapped.Parameter("@code", state.Code));
                command.ExecuteNonQuery();
                stateTransaction = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                stateTransaction = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }

            return stateTransaction;

        }

        public static EnumGenericState DeleteState(State state)
        {
            EnumGenericState stateTransaction = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;

            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_DELETE_STATE, conn);
                command.Parameters.Add(Mapped.Parameter("@code", state.Code));
                command.ExecuteNonQuery();
                stateTransaction = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                stateTransaction = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }

            return stateTransaction;

        }

        public StateDAO()
        {
            //
            // TODO: Add constructor logic here
            //
        }
    }
}